package com.wutuo.aggragation.expire.dao;

import com.wutuo.aggragation.expire.domain.ExpireManagement;

import java.util.Map;

import static org.apache.ibatis.jdbc.SqlBuilder.*;

public class ExpireManagementSqlProvider {

    public String listLatestByInstitutionIdOth(Map<String, Object> params) {


        StringBuffer sb = new StringBuffer();
        sb.append("select a.id as roster_id, a.`name` as roster_name, a.photo as roster_img, a.year, a.grade, c.expire_date, b.sms_date, b.id from roster a ");
        sb.append("left join wt_expire_management_latest b on a.id = b.roster_id ");
        sb.append("left join wt_expire_management c on b.expire_management_id = c.id ");
        sb.append("where a.tg_zt = 'ZT' and a.del_flag = '1' and a.cm_institution_id = #{institutionId} ");
        sb.append("and (a.year < #{endYear} or a.year > #{startYear} or a.year is null) ");
        sb.append("order by c.expire_date desc ");
        sb.append("LIMIT #{index}, #{size} ");
        return sb.toString();
    }

    public String listLatestByInstitutionId(Map<String, Object> params) {

        Object year = params.get("year");

        StringBuffer sb = new StringBuffer();
        sb.append("select a.id as roster_id, a.`name` as roster_name, a.photo as roster_img, a.year, a.grade, c.expire_date, b.sms_date, b.id from roster a ");
        sb.append("left join wt_expire_management_latest b on a.id = b.roster_id ");
        sb.append("left join wt_expire_management c on b.expire_management_id = c.id ");
        sb.append("where a.tg_zt = 'ZT' and a.del_flag = '1' and a.cm_institution_id = #{institutionId} ");
        if (year != null) {
            sb.append("and a.year = #{year} ");
        }
        sb.append("order by c.expire_date desc ");
        sb.append("LIMIT #{index}, #{size} ");
        return sb.toString();
    }

    public String listByRosterId(Map<String, Object> params) {
        StringBuffer sb = new StringBuffer();
        sb.append("select ");
        sb.append("id, create_by, create_date, update_by, update_date, remarks, del_flag, institution_id, ");
        sb.append("roster_id, expire_date, start_date, fee ");
        sb.append("from wt_expire_management ");
        sb.append("where roster_id = #{rosterId,jdbcType=BIGINT} and del_flag = '1' and institution_id = #{institutionId} ");
        sb.append("order by create_date desc ");
        sb.append("limit #{index}, #{size} ");

        return sb.toString();
    }

    public String insertSelective(ExpireManagement record) {
        BEGIN();
        INSERT_INTO("wt_expire_management");

        if (record.getId() != null) {
            VALUES("id", "#{id,jdbcType=BIGINT}");
        }

        if (record.getCreateBy() != null) {
            VALUES("create_by", "#{createBy,jdbcType=BIGINT}");
        }

        if (record.getCreateDate() != null) {
            VALUES("create_date", "#{createDate,jdbcType=TIMESTAMP}");
        }

        if (record.getUpdateBy() != null) {
            VALUES("update_by", "#{updateBy,jdbcType=BIGINT}");
        }

        if (record.getUpdateDate() != null) {
            VALUES("update_date", "#{updateDate,jdbcType=TIMESTAMP}");
        }

        if (record.getRemarks() != null) {
            VALUES("remarks", "#{remarks,jdbcType=VARCHAR}");
        }

        if (record.getDelFlag() != null) {
            VALUES("del_flag", "#{delFlag,jdbcType=CHAR}");
        }

        if (record.getInstitutionId() != null) {
            VALUES("institution_id", "#{institutionId,jdbcType=BIGINT}");
        }

        if (record.getRosterId() != null) {
            VALUES("roster_id", "#{rosterId,jdbcType=BIGINT}");
        }

        if (record.getExpireDate() != null) {
            VALUES("expire_date", "#{expireDate,jdbcType=DATE}");
        }

        if (record.getStartDate() != null) {
            VALUES("start_date", "#{startDate,jdbcType=DATE}");
        }

        if (record.getFee() != null) {
            VALUES("fee", "#{fee,jdbcType=DECIMAL}");
        }

        return SQL();
    }

    public String updateByPrimaryKeySelective(ExpireManagement record) {
        BEGIN();
        UPDATE("wt_expire_management");

        if (record.getCreateBy() != null) {
            SET("create_by = #{createBy,jdbcType=BIGINT}");
        }

        if (record.getCreateDate() != null) {
            SET("create_date = #{createDate,jdbcType=TIMESTAMP}");
        }

        if (record.getUpdateBy() != null) {
            SET("update_by = #{updateBy,jdbcType=BIGINT}");
        }

        if (record.getUpdateDate() != null) {
            SET("update_date = #{updateDate,jdbcType=TIMESTAMP}");
        }

        if (record.getRemarks() != null) {
            SET("remarks = #{remarks,jdbcType=VARCHAR}");
        }

        if (record.getDelFlag() != null) {
            SET("del_flag = #{delFlag,jdbcType=CHAR}");
        }

        if (record.getInstitutionId() != null) {
            SET("institution_id = #{institutionId,jdbcType=BIGINT}");
        }

        if (record.getRosterId() != null) {
            SET("roster_id = #{rosterId,jdbcType=BIGINT}");
        }

        if (record.getExpireDate() != null) {
            SET("expire_date = #{expireDate,jdbcType=DATE}");
        }

        if (record.getStartDate() != null) {
            SET("start_date = #{startDate,jdbcType=DATE}");
        }

        if (record.getFee() != null) {
            SET("fee = #{fee,jdbcType=DECIMAL}");
        }

        WHERE("id = #{id,jdbcType=BIGINT}");

        return SQL();
    }
}